<%@ page import="java.sql.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.io.*" %>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm";

Connection connection = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()) {
String newAccountName = null;
String newISBN = null;
String newBookTitle = null;
String newEdition = null;
String newCourses = null;
String newAuthors = null;
String newPublisher = null;
String newCopyCondition = null;

newAccountName = request.getParameter("username");
newISBN = request.getParameter("isbn");
newBookTitle = request.getParameter("booktitle");
newEdition = request.getParameter("edition");
newCourses = request.getParameter("courses");
newAuthors = request.getParameter("authors");
newPublisher = request.getParameter("publisher");
newCopyCondition = request.getParameter("copycondition");

int count_user = 0;
Statement statement0 = null;
ResultSet rs0 = null;
statement0 = connection.createStatement();
String query0 = "SELECT COUNT(*) AS Count FROM Users WHERE AccountName = '" + newAccountName + "';";
rs0 = statement0.executeQuery(query0);
if(rs0.next()) {
count_user = Integer.parseInt(rs0.getString("Count"));

}
statement0.close();

if(count_user == 0) {
out.print("user_not_logged_in");

}
else {
//check whether newISBN already exists
int count_existing = 0;
Statement statementE = null;
ResultSet rsE = null;
statementE = connection.createStatement();
String queryE = "SELECT count(*) AS Count FROM Books WHERE Books.ISBN = '" + newISBN + "';";
rsE = statementE.executeQuery(queryE);

if(rsE.next()){
count_existing = Integer.parseInt(rsE.getString("Count"));

}
statementE.close();

if(count_existing != 0) {
out.print("new_isbn_already_exists");

}
else {
Statement statement1 = null;
statement1 = connection.createStatement();
String query1 = "INSERT INTO Books VALUES ('" + newBookTitle + "', " + newEdition + ", '" + newCourses + "', '" + newAuthors + "', '" + newPublisher + "', '" + newISBN + "');";
statement1.executeUpdate(query1, Statement.NO_GENERATED_KEYS);
statement1.close();

int newCopyID = 0;
Statement statement2 = null;
statement2 = connection.createStatement();
String query2 = "INSERT INTO BookCopies VALUES (DEFAULT, '" + newISBN + "', '" + newCopyCondition + "', DEFAULT, 'BORROW', '" + newAccountName + "', DEFAULT);";
statement2.executeUpdate(query2, Statement.RETURN_GENERATED_KEYS);
ResultSet rs2 = statement2.getGeneratedKeys();
if(rs2.next()) {
newCopyID = rs2.getInt(1);

}
statement2.close();

int count_record = 0;
Statement statement3 = null;
statement3 = connection.createStatement();
String query3 = "INSERT INTO Lend Values (DEFAULT, '" + newAccountName + "', " + newCopyID + ", DEFAULT, DEFAULT);";
count_record = statement3.executeUpdate(query3, Statement.NO_GENERATED_KEYS);
statement3.close();

int count_listed = 1;
Statement statement4 = null;
ResultSet rs4 = null;
statement4 = connection.createStatement();
String query4 = "SELECT COUNT(*) AS Count FROM Lend WHERE AccountName = '" + newAccountName + "' AND Status <> 'Canceled';"; 
rs4 = statement4.executeQuery(query4);
if(rs4.next()) {
count_listed = Integer.parseInt(rs4.getString("Count"));

}
statement4.close();

if(count_listed % 2 == 0) {
int update_credit = 0;
Statement statement5 = null;
statement5 = connection.createStatement();
String query5 = "UPDATE Users SET Credit = Credit + 1 WHERE AccountName = '" + newAccountName + "';";
update_credit = statement5.executeUpdate(query5, Statement.NO_GENERATED_KEYS);
statement5.close();

}

out.print("insert_successful");

}

}

}
connection.close();

}
catch(Exception ex){
out.print(ex);

}
%>